﻿using System.Data;
using System.Data.OleDb;
using System;

namespace SMS.BaseClass
{

    class ExcelIO
    {
        private OleDbConnection oledbConn;
        private OleDbDataAdapter oledbAdapter;

        public DataTable GetDataFromExcel(string strFileName, string strSheetName, int intExcelVer)
        {
            // Excel连接
            string strConn = string.Empty;

            // 判断EXCEL版本，选取不同的驱动
            if (intExcelVer == CommonConst.EXCEL_VER_2003)
            {
                //2003（Microsoft.Jet.Oledb.4.0）
                strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", strFileName);
            }
            else if (intExcelVer == CommonConst.EXCEL_VER_2007)
            {
                //2007（Microsoft.ACE.OLEDB.12.0）
                strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", strFileName);
            }


            strSheetName += "$";
            string strSqlSelect = "SELECT * FROM [" + strSheetName + "]";   //A1:IV65536

            oledbConn = new OleDbConnection(strConn);
            oledbAdapter = new OleDbDataAdapter(strSqlSelect, oledbConn);
            DataTable dtExcel = null;
            try
            {
                dtExcel = new DataTable();
                oledbConn.Open();
                oledbAdapter.Fill(dtExcel);
            }
            catch (Exception exc)
            {
                throw exc;
                
            }
            finally
            {
                if (oledbConn.State == ConnectionState.Open)
                    oledbConn.Close();
            }

            return dtExcel;
        }
    }

}